package models;

import models.admin.AdminUser;
import models.constants.DeletedStatus;
import models.enums.AdminStockInPlanType;
import order.AdminOrderItem;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import product.ProductSpec;
import util.common.ConvertUtil;
import util.list.ListUtil;

import javax.persistence.*;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by liming on 16/7/16.
 * 采购计划
 */
@Entity
@Table(name = "admin_stock_in_plan")
public class AdminStockInPlan extends BaseModel {
    /**
     * 采购计划编号
     */
    @Column(name = "code")
    public String code;

    /**
     * 总金额
     */
    @Column(name = "total_amount")
    public Double totalAmount = 0D;


    /**
     * 类型 系统 手动
     */
    @Enumerated(EnumType.STRING)
    public AdminStockInPlanType adminStockInPlanType ;

    /**
     * 审核人
     */
    @ManyToOne
    @JoinColumn(name = "audit_user_id")
    public AdminUser auditUser;

    /**
     * 审核时间
     */
    @Column(name = "audit_date")
    @Temporal(TemporalType.TIMESTAMP)
    public Date auditDate;

    /**
     * 创建日期
     */
    @Transient
    public String createAtStr;

    /**
     * 备注
     */
    @Column(name = "remark")
    public String remark;

    /**
     * 删除状态
     */
    @Column(name = "deleted_status")
    public DeletedStatus deletedStatus;


    /**
     * 根据Id查询采购申请单
     */
    public static AdminStockInPlan findByInPllanId(long id) {
        return AdminStockInPlan.find("id=? and deletedStatus=?", id, DeletedStatus.UN_DELETED).first();
    }

    /**
     * 加载采购计划产品规格数据
     * @return
     */
    public final static String SQL_STOCK_PLAY_IN_PRODUCT_SPEC =
            "select %s from (" +
                " select a.id, a.bar_code , b.name,c.name as type_name,a.spec,a.unit,a.boxNum,a.d_code, a.color_mark," +
                    "   round(ifnull(d.day_sale,0),2) as day_sale," +
                    "   round(if(ifnull(d.day_sale,0)=0,0,ifnull(e.stock_num,0)/d.day_sale),2) as lasting_days," +
                    "   ifnull(e.stock_num,0) as stock_num," +
                    "   ifnull(f.total_sale_num,0) as total_sale_num," +
                    "   ifnull(e.stock_num,0) - ifnull(f.total_sale_num,0) as diff_num," +
                    "   if(ifnull(d.day_sale,0)=0,1,0) as base_order" +
                " from product_specs a" +
                " left join products b on a.product_id = b.id" +
                " left join admin_product_type c on b.admin_product_type_id = c.id" +
                " left join (" +
                        "select a.split_product_spec_id," +
                                "sum(splite_num)/datediff(now(),min(a.create_at)) as day_sale" +
                        " from v_admin_order_splite a" +
                        " where a.to_track = 1 and a.admin_order_status<100 and a.create_at>=date_sub(curdate(),interval 7 day) and a.create_at<curdate() and a.deleted = 0 and a.to_track=1" +
                        " group by a.split_product_spec_id" +
                " ) d on a.id = d.split_product_spec_id" +
                " left join admin_product_spec_stock e on a.id = e.product_spec_id" +
                " left join (" +
                        "select a.split_product_spec_id," +
                                "sum(a.splite_num) as total_sale_num " +
                        " from v_admin_order_splite a" +
                        " where a.to_track = 1 and a.admin_order_status<50 and a.logistics_status = 'PREPARING' and a.deleted = 0 and a.to_track=1 " +
                        " group by a.split_product_spec_id" +
                " ) f on a.id = f.split_product_spec_id" +
                " where a.deleted = 0  and a.is_audit = 1 and  a.disable_status = 'ENABLE'" +
                 "  and (a.product_classify='BASIC_PRODUCT' or a.product_classify='FINISHED_PRODUCT' or a.product_classify='ACCESSORY')" +
            ") a where %s %s";
    public static Map<String,Object> loadStockInPlanProductSpec(int page, int rows, String sort, String order, String filterRules){

        Map<String, Object> resultMap = new HashMap<>();
        String initOrder ="a.base_order,a.diff_num,a.lasting_days";
        String sqlWhere = ListUtil.initWhereOrderStr("1=1",filterRules,initOrder,sort,order);
        String sql ="";
        String sqlCount ="";
        sql = String.format(SQL_STOCK_PLAY_IN_PRODUCT_SPEC, "*", sqlWhere, "limit " + (page - 1) * rows + "," + rows);
        Query query = ProductSpec.em().createNativeQuery(sql);
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map<String, Object>> resultList = query.getResultList();
        sqlCount = String.format(SQL_STOCK_PLAY_IN_PRODUCT_SPEC, "count(*)", sqlWhere, "");
        Long count = ConvertUtil.toLong(ProductSpec.em().createNativeQuery(sqlCount).getSingleResult());
        resultMap.put("rows", resultList);
        resultMap.put("total", count);
        return resultMap;
    }


    public final static String SQL_PRODUCT_SPEC_MEAT_NEEDS_NUMBER = " " +
            " select g.id , h.name , g.spec , sum(a.buy_number) as buy_number , " +
            " CONCAT(e.spec,' : ', sum(a.buy_number) , '份;                      ') as con " +
            " from admin_order_item a  " +
            " left join admin_order b on a.admin_order = b.id  " +
            " left join goods c on a.goods_id = c.id " +
            " left join product_spec_merchants d on c.serial_id = d.id  " +
            " left join product_specs e on d.product_spec_id = e.id " +
            " left join product_spec_split f on e.id = f.combo_product_spec_id " +
            " left join product_specs g on f.split_product_spec_id = g.id " +
            " left join products h on g.product_id = h.id " +
            " left join admin_product_type i  on h.admin_product_type_id = i.id " +
            " where i.is_get_specs = 1 and  b.admin_order_status < 50 and g.product_classify = 'BASIC_PRODUCT' and b.logistics_status = 'PREPARING' " +
            " and d.deleted = 0 and e.deleted = 0 and g.deleted = 0 and h.deleted = 0 and b.to_track = 1  %s" +
            " group by g.id , h.name , g.spec ,e.id , e.unit ,e.spec" +
            " order by g.id , buy_number desc";

    public static String  getMeatProductSpec (long specId ){
        String str = "";
        String sqlwhere = " and g.id = " + specId ;
        String sqlSelect = String.format(SQL_PRODUCT_SPEC_MEAT_NEEDS_NUMBER ,sqlwhere);
        Query query = AdminOrderItem.em().createNativeQuery(sqlSelect);
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map<String,Object>> resultList = query.getResultList();
        for(int i = 0 ; i < resultList.size() ; i++){
            str += ConvertUtil.toString(resultList.get(i).get("con"));
        }
        return str ;
    }


}
